package dbs

import (
	"database/sql"
	"fmt"
	v1config "gitee.com/scottq/go-framework/src/v1/config"
	"github.com/go-sql-driver/mysql"
	"strings"
)

type IDBMysql interface {
	DB() *sql.DB
	TX() *sql.Tx

	ExecuteSearch(tableName string, fields []string, whereArr []string, whereArgs []interface{}, orderBy []string, pageNum, pageSize int64, rowsHandler SearchRowsHandler) (int64, int64, error)
	ExecuteQuery(tableName string, fields map[string]interface{}, whereArr []string, whereArgs []interface{}, orderBy []string) (bool, error)
	ExecuteCreate(tableName string, fields map[string]interface{}) (int64, error)
	ExecuteUpdate(tableName string, fields map[string]interface{}, whereArr []string, whereArgs []interface{}) (int64, error)

	BeginTrans() (IDBMysql, error)
	CommitTrans() error
	RollbackTrans() error
}

type DBMysql struct {
	//db，正常操作db
	Edb *sql.DB

	//tx，初始为nil，开始事务后为对应事务上下文
	Etx *sql.Tx
}

func (d *DBMysql) DB() *sql.DB {
	return d.Edb
}

func (d *DBMysql) TX() *sql.Tx {
	return d.Etx
}

func NewDBMysql(c v1config.DBConfig, fs ...func(config *mysql.Config)) (*DBMysql, error) {
	conn, err := NewMysqlConn(&c, fs...)
	if err != nil {
		return nil, err
	}

	return &DBMysql{
		Edb: conn,
	}, nil
}

type SearchRowsHandler func(*sql.Rows) error

func (d *DBMysql) ExecuteSearch(tableName string, fields []string, whereArr []string, whereArgs []interface{}, orderBy []string, pageNum, pageSize int64, rowsHandler SearchRowsHandler) (int64, int64, error) {
	var err error
	var total int64

	if pageNum <= 0 {
		pageNum = 1
	}

	if pageSize <= 0 || pageSize > 1000 {
		pageSize = 1000
	}

	whereStr := ""
	if len(whereArr) > 0 {
		whereStr = "WHERE " + strings.Join(whereArr, " AND ")
	}

	countSql := fmt.Sprintf(
		"SELECT COUNT(id) AS total FROM `%s` %s LIMIT 1",
		tableName, whereStr)

	db := d.DB()
	tx := d.TX()

	//total
	var stmt *sql.Stmt
	if tx != nil {
		stmt, err = tx.Prepare(countSql)
	} else {
		stmt, err = db.Prepare(countSql)
	}
	if err != nil {
		return 0, total, err
	}
	defer stmt.Close()

	row := stmt.QueryRow(whereArgs...)
	err = row.Scan(&total)
	if err == sql.ErrNoRows {
		return 0, 0, nil
	} else if err != nil {
		return 0, total, err
	}

	if len(fields) <= 0 {
		fields = append(fields, "*")
	}
	fieldsStr := strings.Join(fields, ",")
	orderByStr := strings.Join(orderBy, ",")
	if orderByStr != "" {
		orderByStr = "ORDER BY " + orderByStr
	}
	searchSql := fmt.Sprintf(
		"SELECT %s FROM `%s` %s %s LIMIT ? OFFSET ?",
		fieldsStr, tableName, whereStr, orderByStr)

	var stmt1 *sql.Stmt
	if tx != nil {
		stmt1, err = tx.Prepare(searchSql)
	} else {
		stmt1, err = db.Prepare(searchSql)
	}
	if err != nil {
		return 0, total, err
	}
	defer stmt1.Close()

	whereArgs = append(whereArgs, pageSize)
	whereArgs = append(whereArgs, pageSize*(pageNum-1))

	rows, err := stmt1.Query(whereArgs...)
	if err != nil {
		return 0, total, err
	}
	defer rows.Close()
	//处理rows
	err = rowsHandler(rows)
	if err != nil {
		return 0, total, err
	}

	return 0, total, nil
}

func (d *DBMysql) ExecuteQuery(tableName string, fields map[string]interface{}, whereArr []string, whereArgs []interface{}, orderBy []string) (bool, error) {
	var err error

	var fieldArr = []string{}
	var scanArr = []interface{}{}
	for k, v := range fields {
		fieldArr = append(fieldArr, fmt.Sprintf("`%s`", k))
		scanArr = append(scanArr, v)
	}

	whereStr := ""
	if len(whereArr) > 0 {
		whereStr = "WHERE " + strings.Join(whereArr, " AND ")
	}
	fieldStr := strings.Join(fieldArr, ",")

	var orderByStr string
	//orderByStr = strings.Join(orderBy, ",")
	//if orderByStr != "" {
	//	orderByStr = "ORDER BY " + orderByStr
	//}
	selectSql := fmt.Sprintf("SELECT %s FROM %s %s %s LIMIT 1",
		fieldStr, tableName, whereStr, orderByStr)

	db := d.DB()
	tx := d.TX()

	var stmt *sql.Stmt
	if tx != nil {
		stmt, err = tx.Prepare(selectSql)
	} else {
		stmt, err = db.Prepare(selectSql)
	}
	if err != nil {
		return false, err
	}
	defer stmt.Close()

	row := stmt.QueryRow(whereArgs...)
	err = row.Scan(scanArr...)
	if err == sql.ErrNoRows {
		return false, nil
	} else if err != nil {
		return false, err
	}

	return true, nil
}

func (d *DBMysql) ExecuteCreate(tableName string, fields map[string]interface{}) (int64, error) {
	var err error

	var fieldArr = []string{}
	var valueArr = []interface{}{}
	for k, v := range fields {
		fieldArr = append(fieldArr, fmt.Sprintf("`%s`=?", k))
		valueArr = append(valueArr, v)
	}

	fieldStr := strings.Join(fieldArr, ",")

	db := d.DB()
	tx := d.TX()

	insertSql := fmt.Sprintf("INSERT INTO %s SET %s", tableName, fieldStr)
	var stmt *sql.Stmt
	if tx != nil {
		stmt, err = tx.Prepare(insertSql)
	} else {
		stmt, err = db.Prepare(insertSql)
	}

	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	ret, err := stmt.Exec(valueArr...)
	if err != nil {
		return 0, err
	}

	return ret.LastInsertId()
}

func (d *DBMysql) ExecuteUpdate(tableName string, fields map[string]interface{}, whereArr []string, whereArgs []interface{}) (int64, error) {
	var err error

	var fieldArr = []string{}
	var valueArr = []interface{}{}
	for k, v := range fields {
		fieldArr = append(fieldArr, fmt.Sprintf("`%s`=?", k))
		valueArr = append(valueArr, v)
	}

	fieldStr := strings.Join(fieldArr, ",")

	whereStr := ""
	if len(whereArr) > 0 {
		whereStr = "WHERE " + strings.Join(whereArr, " AND ")
	}

	if len(whereArgs) > 0 {
		for _, v := range whereArgs {
			valueArr = append(valueArr, v)
		}
	}
	db := d.DB()
	tx := d.TX()

	updateSql := fmt.Sprintf("UPDATE %s SET %s %s", tableName, fieldStr, whereStr)
	var stmt *sql.Stmt
	if tx != nil {
		stmt, err = tx.Prepare(updateSql)
	} else {
		stmt, err = db.Prepare(updateSql)
	}
	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	ret, err := stmt.Exec(valueArr...)
	if err != nil {
		return 0, err
	}

	return ret.RowsAffected()
}

func (d *DBMysql) BeginTrans() (IDBMysql, error) {
	tx, err := d.Edb.Begin()
	if err != nil {
		return nil, err
	}
	return &DBMysql{
		Edb: nil,
		Etx: tx,
	}, nil
}

func (d *DBMysql) CommitTrans() error {
	if d.Etx == nil {
		return fmt.Errorf("not begin trans")
	}
	return d.Etx.Commit()
}

func (d *DBMysql) RollbackTrans() error {
	if d.Etx == nil {
		return fmt.Errorf("not begin trans")
	}
	return d.Etx.Rollback()
}

func (d *DBMysql) WithDB(db *sql.DB) *DBMysql {
	return &DBMysql{
		Edb: db,
	}
}
